package com.match;

import com.dao.TableDao;
import com.dao.TableDataDao;
import com.dao.impl.TableDaoImpl;
import com.dao.impl.TableDataDaoImpl;
import com.file.FileManager;
import com.models.*;
import com.util.Constants;
import com.util.Error;

import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * Created by TimeLiar on 2015/9/24.
 * Biu~~Biu~~~Biu~~~~
 */
public class SqlFunctions {
    private TableDao tableDao = new TableDaoImpl();
    private TableDataDao tableDataDao = new TableDataDaoImpl();
    private SqlTableMethod sqlTableMethod = new SqlTableMethod();

    //create table a (...)
    public void create(String sql) throws Error {
        if (sql.split(" ").length < 3 || !sql.split(" ")[0].equals("create")) {
            throw new Error(sql.split(" "), 0, "incorrect match string");
        }
        //TODO table
        if (sql.split(" ")[1].equals("table")) {
            try {
                Table table = new Table();
                String tableName = sql.split(" ")[2];
                table.setName(tableName);
                String columns = sql.substring(sql.indexOf("(") + 1, sql.lastIndexOf(")"));
                String[] column = columns.split(",");
                boolean pkey = false;
                for (String item : column) {
                    String[] piece = item.split(" ");
                    switch (piece.length) {
                        case 0:
                        case 1:
                            continue;
                        case 2: {
                            String colName = piece[0];
                            int length = 11;
                            String type = piece[1];
                            if (type.contains("(") && type.contains(")")) {
                                length = Integer.parseInt(type.substring(type.indexOf("(") + 1, type.indexOf(")")));
                                type = type.substring(0, type.indexOf("(") - 1);
                            }
                            Column col = new Column(colName, type, length, false, false);
                            table.getColumns().add(col);
                            break;
                        }
                        case 3: {
                            String colName = piece[0];
                            int length = 11;
                            String type = piece[1];
                            if (type.contains("(") && type.contains(")")) {
                                length = Integer.parseInt(type.substring(type.indexOf("(") + 1, type.indexOf(")")));
                                type = type.substring(0, type.indexOf("(") - 1);
                            }
                            boolean pk = false;
                            boolean nn = false;
                            if (piece[2].equals("primarykey") && !pkey) {
                                pk = true;
                                pkey = true;
                            }
                            if (piece[2].equals("notnull")) {
                                nn = true;
                            }
                            Column col = new Column(colName, type, length, pk, nn);
                            table.getColumns().add(col);
                            break;
                        }
                        default:
                            break;
                    }
                }
                sqlTableMethod.createTable(table);
            } catch (Exception e) {
                System.out.println(e.getMessage());
                throw e;
            }
            //create index on ${table} ([${column}])
            //TODO index
        } else if (sql.split(" ")[1].equals("index")) {
            String[] divide = sql.split(" ");
            if (divide.length < 5) {
                throw new Error();
            }
            TreeSet<IndexNode> nodeTree = new TreeSet<>();
            TableData data = tableDataDao.getDate(divide[3]);
            if (data == null) {
                System.out.println("table '" + divide[3] + "' not found");
                return;
            }
            Table table = data.getTable();
            int position = -1;
            try {
                for (Column column : table.getColumns()) {
                    if (column.getName().equals(divide[4].substring(1, divide[4].length() - 1))) {
                        position = table.getColumns().indexOf(column);
                        break;
                    }
                }
            } catch (Exception e) {
                throw new Error();
            }
            if (position > -1) {
                for (int i = 0; i < data.getRows().size(); i++) {
                    IndexNode indexNode = new IndexNode();
                    indexNode.setData(data.getRows().get(i).get(position));
                    indexNode.setIdx(i);
                    //System.out.println(indexNode);
                    //System.out.println(nodeTree.add(indexNode));
                    nodeTree.add(indexNode);
                }
                try {
                    FileManager.writeObject(nodeTree, Constants.INDEX_TEMP.replace("*", table.getColumns().get(position).getName() + "%" + divide[3] + "%"));
                } catch (IOException e) {
                    e.printStackTrace();
                }
                ArrayList<String[]> resRow = new ArrayList<>();
                String[] title = {"Index", "Data"};
                for (IndexNode in : nodeTree) {
                    String[] temp = {String.valueOf(in.getIdx()), in.getData()};
                    resRow.add(temp);
                }
                System.out.println(sqlTableMethod.showData(resRow, title));
            } else {
                System.out.println("column '" + divide[4].substring(1, divide[4].length() - 1) + "' not found");
            }

        }
    }

    //insert into a set id=1,name=2
    @SuppressWarnings("unchecked")
    public void insert(String sql) throws Error {
        String[] divide = sql.split(" ");
        if (divide[1].equals("into")) {
            String tableName = divide[2];
            String[] keys = divide[3].substring(divide[3].indexOf("(") + 1,
                    divide[3].indexOf(")")).split(",");
            String[] values = divide[5].substring(
                    divide[5].indexOf("(") + 1, divide[5].indexOf(")"))
                    .split(",");
            Map<String, String> map = new HashMap<>();
            for (int i = 0; i < keys.length; i++) {
                map.put(keys[i], values[i]);
            }
            Table table = tableDao.getTable(tableName);
            if (table == null) {
                System.out.println("table '" + divide[2] + "' not found");
                return;
            }
            TableData data = tableDataDao.getDate(tableName);
            if (data == null) {
                data = new TableData(table);
            }
            ArrayList<String> row = new ArrayList<>();
            for (Column column : table.getColumns()) {
                if (map.keySet().contains(column.getName())) {
                    if (map.get(column.getName()).length() > column.getLength()) {
                        System.out.println("data too long for " + column.getName());
                        continue;
                    }
                    row.add(map.get(column.getName()));
                } else {
                    row.add(null);
                }
            }
            data.insertRow(row);
            tableDataDao.updateDate(data);
            File dir = new File(Constants.INDEX_HOME);
            if (!dir.isDirectory()) {
                return;
            }
            for (String fName : dir.list()) {
                if (fName.contains("%" + tableName + "%")) {
                    String colName = fName.substring(0, fName.indexOf("%"));
                    try {
                        create("create index on " + tableName + " (" + colName + ")");
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            System.out.println("insert success");
        }
    }

    public void desc(String sql) throws Error {
        String[] pieces = sql.split(" ");
        if (pieces.length != 2) {
            throw new Error(pieces, 1, "unknown");
        }
        System.out.println(sqlTableMethod.tableStructure(pieces[1]));
    }


    //TODO alter
    //  0     1   2  3   4
    //alter table s add (id int primarykey)
    //alter table s drop a
    public void alter(String sql) {
        String[] divide = sql.split(" ");
        if (divide[1].equals("table")) {
            Table table = tableDao.getTable(divide[2]);
            if (table != null) {
                if (divide[3].equals("add")) {
                    String columns = sql.substring(sql.indexOf("(") + 1,
                            sql.lastIndexOf(")"));
                    ArrayList<Column> columnList = table.getColumns();
                    String colum[] = columns.split(",");
                    boolean pk = false;
                    for (Column column : columnList) {
                        if (column.isPrimaryKey()) {
                            pk = true;
                            break;
                        }
                    }
                    for (String items : colum) {
                        Column column = new Column();
                        String item[] = items.split(" ");
                        String columnName = item[0];
                        String columnType = item[1];
                        int length = 11;
                        boolean isPk = false;
                        if (item.length >= 3 && !pk
                                && item[2].equals("primarykey")) {
                            isPk = true;
                            pk = true;
                        }
                        try {
                            length = Integer.parseInt(columnType.substring(
                                    columnType.indexOf("(") + 1,
                                    columnType.indexOf(")")));
                        } catch (Exception ignore) {
                        }
                        if (columnType.contains("(")
                                && columnType.contains(")")) {
                            columnType = columnType.substring(0,
                                    columnType.indexOf("("));
                        }
                        column.setName(columnName);
                        column.setType(columnType);
                        column.setLength(length);
                        column.setPrimaryKey(isPk);
                        columnList.add(column);
                    }
                    TableData data = tableDataDao.getDate(table.getName());
                    data.setTable(table);
                    for (ArrayList<String> row : data.getRows()) {
                        row.add(null);
                    }
                    tableDataDao.updateDate(data);
                } else if (divide[3].equals("drop")) {
                    String columName = divide[4];
                    Column temp = null;
                    for (Column column : table.getColumns()) {
                        if (column.getName().equals(columName)) {
                            temp = column;
                            break;
                        }
                    }
                    int index = table.getColumns().indexOf(temp);
                    if (temp != null) {
                        table.getColumns().remove(temp);
                    }
                    TableData data = tableDataDao.getDate(table.getName());
                    data.setTable(table);
                    for (ArrayList<String> row : data.getRows()) {
                        row.remove(index);
                    }
                    tableDataDao.updateDate(data);
                }
                tableDao.update(table);
                System.out.println("alter success");
            } else {
                System.out.println("no such table");
            }
        }

    }

    //update a set name=2 where id=1
    public void update(String sql) {
        String divide[] = sql.split(" ");
        if (divide[2].equals("set")) {
            TableData data = tableDataDao.getDate(divide[1]);
            Table table = tableDao.getTable(divide[1]);
            if (table == null || data == null) {
                System.out.println("table '" + divide[1] + "' not found");
                return;
            }
            String[] res = divide[3].split(",");
            String[] condition = divide[5].split("=");
            Map<Integer, String> operator = new HashMap<>();
            // map the position of id ,link the position and id

            int[] index = new int[res.length];
            for (int i = 0; i < res.length; i++) {
                for (Column column : table.getColumns()) {
                    if (column.getName().equals(res[i].split("=")[0])) {
                        index[i] = table.getColumns().indexOf(column);
                        break;
                    }
                }
            }
            for (int i = 0; i < index.length; i++) {
                operator.put(index[i], res[i].split("=")[1]);
            }//link value & index[i]
            int condIndex = 0;
            for (Column column : table.getColumns()) {
                if (column.getName().equals(condition[0])) {
                    condIndex = table.getColumns().indexOf(column);
                    break;
                }
            }
            for (ArrayList<String> row : data.getRows()) {
                if (row.get(condIndex).equals(condition[1])) {
                    for (int i : index) {
                        if (row.size() <= i) {
                            int dis = i - row.size();
                            for (int j = 0; j < dis; j++) {
                                row.add(null);
                            }
                            row.add(operator.get(i));
                        } else {
                            row.set(i, operator.get(i));
                        }
                    }
                }
            }
            tableDataDao.updateDate(data);
            System.out.println("update success");
        }
    }

    //delete from a where id=1
    public void delete(String sql) throws Error {
        String[] divide = sql.split(" ");
        TableData data = tableDataDao.getDate(divide[2]);
        Table table = tableDao.getTable(divide[2]);
        if (divide[1].equals("from") && divide.length == 3) {
            if (data != null) {
                data.getRows().clear();
                tableDataDao.updateDate(data);
                System.out.println("delete success");
            } else {
                System.out.println("table '" + divide[2] + "' not found");
            }
        } else if (divide.length == 5) {
            String condStr = divide[4];
            String key = condStr.split("=")[0];
            String value = condStr.split("=")[1];
            int index = -1;
            for (Column col : table.getColumns()) {
                if (col.getName().equals(key)) {
                    index = table.getColumns().indexOf(col);
                    break;
                }
            }
            ArrayList<ArrayList<String>> toDelete = new ArrayList<>();
            for (ArrayList<String> row : data.getRows()) {
                if (row.get(index).equals(value)) {
                    toDelete.add(row);
                }
            }
            for (ArrayList<String> row : toDelete) {
                data.getRows().remove(row);
            }
            tableDataDao.updateDate(data);
            System.out.println("delete success");
        }
    }

    public void drop(String sql) {
        String[] pieces = sql.split(" ");
        if (pieces.length < 3) {
            return;
        }
        if (pieces[1].equals("table")) {
            sqlTableMethod.deleteTable(pieces[2]);
        } else if (pieces[1].equals("index")) {
            if (pieces.length < 4) {
                return;
            }
            String fileName = pieces[3] + "%" + pieces[2] + "%";
            File file = new File(Constants.INDEX_TEMP.replace("*", fileName));
            if (!file.exists()) {
                System.out.println("index not found");
            }
            if (!file.delete()) {
                System.out.println("drop index failed");
            }
        }

    }

    public void select(String sql) throws Error {
        String[] divide = sql.split(" ");
        //select * from a,b where a.id=b.id and address=2;
        if (divide[1].equals("*") && divide[2].equals("from")) {
            String[] tables = divide[3].split(",");
            Table[] ts = new Table[tables.length];
            TableData[] tds = new TableData[tables.length];
            for (int i = 0; i < tables.length; i++) {
                ts[i] = tableDao.getTable(tables[i]);
                if (ts[i] == null) {
                    System.out.println("table '" + tables[i] + "' not found");
                    return;
                }
                tds[i] = tableDataDao.getDate(tables[i]);
            }
            int colSum = 0;
            for (Table table : ts) {
                colSum += table.getColumns().size();
            }
            String[] colSet = new String[colSum];
            ArrayList<String[]> dRows = new ArrayList<>();
            int flag = 0;
            for (Table table : ts) {
                for (int i = flag; i < table.getColumns().size() + flag; i++) {
                    colSet[i] = table.getColumns().get(i - flag).getName();
                }
                flag += table.getColumns().size();
            }
            if (tds.length == 2) {
                for (ArrayList<String> row1 : tds[0].getRows()) {
                    for (ArrayList<String> row2 : tds[1].getRows()) {
                        String[] row = new String[colSum];
                        for (int i = 0; i < row1.size(); i++) {
                            row[i] = row1.get(i);
                        }
                        flag = row1.size();
                        for (int i = flag; i < row2.size() + flag; i++) {
                            row[i] = row2.get(i - flag);
                        }
                        dRows.add(row);
                    }
                }
            } else if (tds.length == 1) {
                for (ArrayList<String> row : tds[0].getRows()) {
                    String[] res = new String[row.size()];
                    for (int i = 0; i < res.length; i++) {
                        res[i] = row.get(i);
                    }
                    dRows.add(res);
                }
            }
            if (divide.length <= 4 || !divide[4].equals("where")) {
                System.out.println(sqlTableMethod.showData(dRows, colSet));
                System.out.println(dRows.size() + " rows in set");
            } else {
                //TODO select * from a,b where a.id=b.id and a.id=1
                if (divide.length == 6) {
                    String condition = divide[5];
                    //a.id=b.id
                    String[] keyMap = condition.split("=");
                    if (keyMap.length != 2) {
                        throw new Error();
                    }
                    if (keyMap[0].split(".").length == 2) {
                        String tName = keyMap[0].split(".")[0];
                        String propName = keyMap[0].split(".")[1];
                        Table table = tableDao.getTable(tName);
//TODO
                    } else {
                        throw new Error();
                    }
                }
            }
            //select a.id,b,id from a,b;
        } else if (!Objects.equals(divide[1], "*") && divide[2].equals("from")) {
            Table table = tableDao.getTable(divide[3]);
            if (table == null) {
                System.out.println("table '" + divide[3] + "' not found");
                return;
            }
            TableData data = tableDataDao.getDate(divide[3]);
            if (data == null) {
                System.out.println("empty set");
                return;
            }
            String cols[] = divide[1].split(",");
            int[] index = new int[cols.length];//index:column's position for search
            for (int i = 0; i < index.length; i++) {
                index[i] = -1;
            }
            for (int i = 0; i < cols.length; i++) {
                for (Column column : table.getColumns()) {
                    if (column.getName().equals(cols[i])) {
                        index[i] = table.getColumns().indexOf(column);
                        break;
                    }
                }
            }
            if (Arrays.toString(index).equals("[-1]")) {
                System.out.println("no such column");
                return;
            }
            ArrayList<String[]> resRows = new ArrayList<>();
            for (ArrayList<String> row : data.getRows()) {
                String[] res = new String[index.length];
                for (int i = 0; i < res.length; i++) {
                    res[i] = row.get(index[i]);
                }
                resRows.add(res);
            }
            System.out.println(sqlTableMethod.showData(resRows, cols));
            System.out.println(resRows.size() + " rows in set");
        }
    }

    //source ${path}
    public void source(String sqlString) throws Error {
        String[] divide = sqlString.split(" ");
        try {
            String content = FileManager.readFile(divide[1]);
            if (content == null) {
                return;
            }
            String[] eachSQL = content.split(";");
            SqlParse parse = new SqlParse();
            for (String sql : eachSQL) {
                parse.setSqlString(sql);
                parse.parse();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void truncate(String sql) throws Error {
        String[] divide = sql.split(" ");
        if (divide.length != 2) {
            throw new Error();
        }
        TableData data = tableDataDao.getDate(divide[1]);
        data.getRows().clear();
        tableDataDao.updateDate(data);
    }

    public void show(String sql) throws Error {
        String[] divide = sql.split(" ");
        if (divide.length != 2) {
            throw new Error(divide, 1, "unknown");
        }
        if (divide[1].equals("tables")) {
            ResultView view = new SqlTableMethod().showTables();
            System.out.printf(view.toString());
        } else if (divide[1].equals("index")) {
            String[] title = {"Table", "Column"};
            File dir = new File(Constants.INDEX_HOME);
            if (!dir.isDirectory()) {
                return;
            }
            ArrayList<String[]> resRows = new ArrayList<>();
            for (String indexFile : dir.list()) {
                String[] row = {
                        indexFile.substring(indexFile.indexOf("%") + 1, indexFile.lastIndexOf("%")),
                        indexFile.substring(0, indexFile.indexOf("%"))
                };
                resRows.add(row);
            }
            System.out.println(sqlTableMethod.showData(resRows, title));
            System.out.println(resRows.size() + " rows in set");
        }
    }

    //TODO check ${table} ${column}
    @SuppressWarnings("unchecked")
    public void check(String sql) {
        String[] divide = sql.split(" ");
        if (divide.length != 3) {
            return;
        }
        String fileName = divide[2] + "%" + divide[1] + "%";
        TreeSet<IndexNode> nodeSet;
        try {
            nodeSet = (TreeSet<IndexNode>) FileManager.readObject(Constants.INDEX_TEMP.replace("*", fileName));
        } catch (IOException | ClassNotFoundException e) {
            System.out.println("index nt found");
            return;
        }
        if (nodeSet == null) {
            return;
        }
        ArrayList<String[]> resRow = new ArrayList<>();
        String[] title = {"Index", "Data"};
        for (IndexNode in : nodeSet) {
            String[] temp = {String.valueOf(in.getIdx()), in.getData()};
            resRow.add(temp);
        }
        System.out.println(sqlTableMethod.showData(resRow, title));
        System.out.println(resRow.size() + " rows in set");
    }
}
